use std::{borrow::Cow, collections::HashMap};

use async_trait::async_trait;
use serde_json::Value;
use shq_common::prelude::{
    report::{DisplayCols, ReportDataResponse, ReportDefine, ReportGroup},
    ColumnCell, OrderItems, WhereCauses,
};

use super::{get_display_cols, parse_where, ListReport};
use crate::{
    database::{
        get_cross_columns, get_leaf_columns, get_low_cardinality_col_options, parse_order,
        DrillDownRequ, SqlExecutor, TableReader,
    },
    error::{Error, Result},
    export::{export_muliti_xlsx_zip, export_xlsx},
    get_rept_config,
    report::to_header_cells,
    Column, DataSourceType, LowCardinality,
};
pub struct CrossReport<'a> {
    list_rept: ListReport<'a>,
}
impl<'a> CrossReport<'a> {
    pub async fn from(
        rept_name: &'a str,
        params: Vec<(&'a str, String)>,
        user_id: u32,
        menu_id: u32,
    ) -> Result<Self> {
        let rept_conf =
            get_rept_config(rept_name).ok_or(Error::from_create(rept_name, "报表不存在"))?;
        let list_rept = ListReport {
            rept_name,
            rept_conf,
            params,
            user_id,
            menu_id,
            tmp_table_name: None,
        };
        let mut rept = CrossReport { list_rept };
        rept.create().await?;
        Ok(rept)
    }
    async fn create(&mut self) -> Result<()> {
        let rept_conf = &self.list_rept.rept_conf;
        let params = self
            .list_rept
            .params
            .iter()
            .map(|p| (p.0, p.1.as_str()))
            .collect::<Vec<_>>();
        let se = SqlExecutor::new(&self.list_rept.rept_name, params.as_slice());
        let tname = match rept_conf.data_source.source_type {
            DataSourceType::Table => {
                let sql_str = if let Some(wc) = &rept_conf.data_source.where_cause {
                    format!(
                        "select * from {} where {}",
                        rept_conf.data_source.content, wc
                    )
                } else {
                    rept_conf.data_source.content.to_owned()
                };
                se.exec_cross(
                    &sql_str,
                    rept_conf.columns.as_slice(),
                    rept_conf.rows.as_slice(),
                    rept_conf.cells.as_slice(),
                    rept_conf.column_levels.as_slice(),
                    rept_conf.groups.as_slice(),
                    &rept_conf.unique_columns,
                    &rept_conf.params.as_slice(),
                    rept_conf.unit.clone(),
                )
                .await?
            }
            DataSourceType::Sql => {
                se.exec_cross(
                    &rept_conf.data_source.content,
                    rept_conf.columns.as_slice(),
                    rept_conf.rows.as_slice(),
                    rept_conf.cells.as_slice(),
                    rept_conf.column_levels.as_slice(),
                    rept_conf.groups.as_slice(),
                    &rept_conf.unique_columns,
                    &rept_conf.params.as_slice(),
                    rept_conf.unit.clone(),
                )
                .await?
            }
            _ => unreachable!(),
        };
        self.list_rept.tmp_table_name = Some(tname.to_string());
        Ok(())
    }
    async fn load_data_list(
        &self,
        where_causes: Option<WhereCauses>,
        orders: Option<OrderItems>,
        display_cols: DisplayCols,
        group_index: u8,
        drill_downs: Vec<(u8, Value)>,
        extra_where: Option<String>,
        page: u32,
        size: u16,
        has_front_must: bool,
    ) -> Result<ReportDataResponse> {
        let rept_conf = self.list_rept.rept_conf;
        if self.list_rept.tmp_table_name.is_none() {
            return Err(Error::from_read(self.list_rept.rept_name, "报表未创建"));
        }
        let glen = rept_conf.groups.len() as u8;
        if group_index != 0 && group_index >= glen as u8 {
            return Err(Error::from_read(
                self.list_rept.rept_name,
                "分组索引group_index越界",
            ));
        }
        let tmp_table_name = self.list_rept.tmp_table_name.as_ref().unwrap();
        let cols = get_cross_columns(tmp_table_name)?;
        let leaf_cols = get_leaf_columns(cols.as_slice());
        let row_range =
            shq_auth::get_row_range(self.list_rept.user_id, self.list_rept.menu_id).await?;
        let where_cause = parse_where(
            rept_conf.auth_filter.as_ref(),
            leaf_cols.as_slice(),
            rept_conf.groups.as_slice(),
            where_causes,
            row_range,
            extra_where,
            self.list_rept.user_id,
            group_index,
            &self.list_rept.rept_conf.unit,
        );
        let def_order: Cow<str> = if rept_conf.groups.len() > 0 {
            let idx = group_index as usize;
            let v = rept_conf.groups[idx].by.as_str();
            Cow::Borrowed(v)
        } else {
            let v = rept_conf.unique_columns.join(",");
            if let Some(dord) = rept_conf.default_order.as_deref() {
                Cow::Owned(format!("{dord},{v}"))
            } else {
                Cow::Owned(v)
            }
        };
        let order_by = parse_order(orders.as_ref(), def_order.as_ref());
        let columns = get_display_cols(
            leaf_cols.as_slice(),
            rept_conf.groups.as_slice(),
            &display_cols,
            group_index,
            has_front_must,
        );
        let tr = TableReader::new(tmp_table_name.as_str(), &columns, rept_conf.unit.clone());
        if page < 1 {
            return Err(Error::from_conf(
                self.list_rept.rept_name,
                "page必须从1开始",
            ));
        }
        let offset = (page - 1) as u64 * size as u64;
        let mut res = tr
            .get_page_data(where_cause.as_str(), order_by.as_str(), size, offset)
            .await?;
        let requs = DrillDownRequ::from_vec(drill_downs, &rept_conf.groups);
        let dds = tr.drill_down(requs, orders.as_ref()).await?;
        for dd in dds {
            let gidx = dd.group_index as usize;
            let by_val = dd.by_value;
            let col_name = rept_conf.groups[gidx].by.as_str();
            let idx = columns
                .iter()
                .position(|c| c.name.as_deref().unwrap() == col_name);
            let pos = idx
                .map(|i| res.data.iter().position(|r| r[i] == by_val))
                .flatten();
            if let Some(pos) = pos {
                res.add_drill_down(pos, dd.data);
            }
        }
        Ok(res)
    }
}
#[async_trait]
impl<'a> super::ReportTrait for CrossReport<'a> {
    async fn get_rept_define(&self) -> Result<ReportDefine> {
        let rept_conf = self.list_rept.rept_conf;
        let groups: Vec<ReportGroup> = rept_conf.groups.clone();
        let param_vals = self
            .list_rept
            .params
            .iter()
            .map(|p| (p.0, p.1.as_str()))
            .collect::<Vec<_>>();
        let cols: Vec<Column> = get_cross_columns(self.list_rept.tmp_table_name.as_ref().unwrap())?;
        let mut columns: Vec<ColumnCell> = Vec::new();
        for c in cols.iter() {
            let cell = c.to_column_cell(&[], param_vals.as_slice()).await?;
            columns.push(cell);
        }
        let mut params: Vec<shq_common::prelude::Parameter> = Vec::new();
        for p in rept_conf.params.iter() {
            let mut np = p.clone();
            np.default_value.convert_to_str().await?;
            params.push(np);
        }
        let rdr = ReportDefine {
            title: rept_conf.title.clone(),
            unique_columns: rept_conf.unique_columns.clone(),
            unit: rept_conf.unit.clone(),
            params,
            groups,
            columns,
            chart: None,
            context_menus: None,
            special_rows: None,
            series_source: rept_conf.series_source.clone(),
            has_export_sheet_col: rept_conf.export_sheet_col_name.is_some(),
        };
        Ok(rdr)
    }
    async fn get_options_map(&self) -> Result<HashMap<String, Vec<String>>> {
        let mut options_map: HashMap<String, Vec<String>> = HashMap::new();
        if self.list_rept.tmp_table_name.is_none() {
            return Err(Error::from_conf(self.list_rept.rept_name, "报表未创建"));
        }
        let leaf_cols = get_leaf_columns(&self.list_rept.rept_conf.columns);
        for col in leaf_cols.iter() {
            if col.low_cardinality.is_some() {
                if let Some(name) = col.name.as_deref() {
                    if let Some(options) = get_low_cardinality_col_options(
                        self.list_rept.tmp_table_name.as_ref().unwrap(),
                        name,
                        col.low_cardinality.as_ref().unwrap(),
                    )
                    .await
                    .ok()
                    {
                        options_map.insert(name.to_string(), options);
                    }
                }
            }
        }
        Ok(options_map)
    }
    async fn get_data_list(
        &self,
        where_causes: Option<WhereCauses>,
        orders: Option<OrderItems>,
        display_cols: DisplayCols,
        group_index: u8,
        drill_downs: Vec<(u8, Value)>,
        extra_where: Option<String>,
        page: u32,
        size: u16,
    ) -> Result<ReportDataResponse> {
        self.load_data_list(
            where_causes,
            orders,
            display_cols,
            group_index,
            drill_downs,
            extra_where,
            page,
            size,
            true,
        )
        .await
    }
    async fn export_xslx(
        &self,
        where_causes: Option<WhereCauses>,
        orders: Option<OrderItems>,
        display_cols: DisplayCols,
        group_index: u8,
        drill_downs: Vec<(u8, Value)>,
        extra_where: Option<String>,
        one_sheet_on_file: bool,
    ) -> Result<Vec<u8>> {
        let rept_conf = self.list_rept.rept_conf;
        if self.list_rept.tmp_table_name.is_none() {
            return Err(Error::from_conf(self.list_rept.rept_name, "报表未创建"));
        }
        let tmp_table_name = self.list_rept.tmp_table_name.as_ref().unwrap();
        let cols = get_cross_columns(tmp_table_name)?;
        let leaf_cols = get_leaf_columns(cols.as_slice());
        let columns = get_display_cols(
            leaf_cols.as_slice(),
            rept_conf.groups.as_slice(),
            &display_cols,
            group_index,
            false,
        );
        let dis_cols = columns
            .iter()
            .map(|c| c.name.as_deref().unwrap())
            .collect::<Vec<_>>();
        let params = self
            .list_rept
            .params
            .iter()
            .map(|p| (p.0, p.1.as_str()))
            .collect::<Vec<_>>();
        let data = self
            .load_data_list(
                where_causes,
                orders,
                display_cols,
                group_index,
                drill_downs,
                extra_where,
                1,
                0,
                false,
            )
            .await?;
        let header_cells = to_header_cells(&columns, &dis_cols, &params).await?;
        let title = &rept_conf.title.as_str();
        let special_rows = rept_conf.special_rows.as_ref();
        let conf_params = &rept_conf.params;
        let params = params.as_slice();
        let datas = data.data;
        let sheet_col_name = rept_conf.export_sheet_col_name.as_deref();
        let datas = if one_sheet_on_file && sheet_col_name.is_some() {
            let sheet_col_name = sheet_col_name.unwrap();
            export_muliti_xlsx_zip(
                title,
                header_cells,
                conf_params,
                params,
                datas,
                sheet_col_name,
                special_rows,
            )
            .await?
        } else {
            export_xlsx(
                title,
                header_cells,
                conf_params,
                params,
                datas,
                sheet_col_name,
                special_rows,
            )
            .await?
        };
        Ok(datas)
    }
}
